// 导入数据库操作模块
const db = require('../db/index')

//返回首页统计人数
exports.countSystemUser = (req, res) => {
    query = req.body
    const sql = `select  us.us as "系统用户数量" ,dp.de as "部门总量" , mg.mg as "管理员数量",msg.msg as "消息总量"
    from (select count(id) as us  from users ) us left join (SELECT count(department) as de  from department 
    ) dp on 1=1 left join 
    (
    SELECT count( username ) as mg from (SELECT username from user_manager GROUP BY username ) tb
    )mg on 1=1  LEFT JOIN
    (SELECT count( id ) as msg from message
    )msg on 1=1`

    db.query(sql, (err, results) => {
        if (err) return res.cc(err)
        const keys = Object.keys(results[0])
        const resArr = []
        for (let item = 0; item < keys.length; item++) {
            resArr.push(
                {
                    key: keys[item],
                    value: Object.values(results[0])[item]
                }
            )

        }

        res.send(resArr)
    })

}


//返回统计周期
exports.countSystemLastWeek = (req, res) => {
    query = req.body
    const sql = `SELECT * FROM
    (
    SELECT COUNT(id) AS "newUserOfLastWeek"
    FROM users
    WHERE create_time >= DATE_SUB(CURDATE(), INTERVAL DAYOFWEEK(CURDATE()) - 1 + 7 DAY)
      AND create_time < DATE_SUB(CURDATE(), INTERVAL DAYOFWEEK(CURDATE()) - 1 DAY)
        ) nus 
    left join  
    (
    SELECT COUNT(id) as "alive_user", ( SELECT COUNT(id) as "活跃用户" from users ) as "userCount" from users  WHERE STATUS = 0 
    ) alvus on 1=1
    left join  
    (
    SELECT COUNT(is_login) as islogin from login_log  WHERE is_login = 0
    ) islogin on 1=1 
    left join 
    (SELECT count( id ) as heavy_msg from message WHERE message_level = '必要'
    )msg on 1=1`
    db.query(sql, (err, results) => {
        if (err) return res.cc(err)
        results = results[0]
        res.send(results)
    })

}